/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package pkgUtil.pkgJavaDB;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import pkgModuloPrincipal.pkgConfiguracionRegional.ContextEntity;

/**
 *
 * @author Armando
 */
public class GestorDB {

    private static String CLASS_DRIVER_JAVADB = "org.apache.derby.jdbc.EmbeddedDriver";

    public static void initDB() throws ClassNotFoundException, SQLException, Exception {
        //Cargo la clase Driver
        Connection dbConnection = null;
        try {
            try {
                Class.forName(CLASS_DRIVER_JAVADB);
            } catch (ClassNotFoundException ex) {
                throw new ClassNotFoundException("initDB: Error en la carga de clase Driver par DB: " + ex.getMessage());
            }
            //Obtengo conexion  
            dbConnection = null;
            try {
                dbConnection = ConnectionDB.getConnection(true);
                if (dbConnection == null) {
                    throw new SQLException("initDB: No se pudo obtener conexion a BD PAS");
                }
            } catch (SQLException ex) {
                throw new SQLException("initDB: Error en obtención de conexión a BD PAS: " + ex.getMessage());
            }

            try {
                ShemaDB.createShema(dbConnection);
            } catch (SQLException ex) {
                throw new SQLException("initDB: Error en creacion de Shema PAS: " + ex.getMessage());
            }
        } catch (Exception ex) {
            throw new Exception("initDB: Error en inicializacion de base de datos PAS: " + ex.getMessage());
        } finally {
            if (dbConnection != null && !dbConnection.isClosed()) {
                dbConnection.close();
            }

        }
    }

    public static ContextEntity getConfiguracionContexto() throws SQLException {
        PreparedStatement stmtSelect = null;
        Connection dbConnection = null;
        ResultSet rsSel = null;
        try {
            dbConnection = ConnectionDB.getConnection(false);
            stmtSelect = dbConnection.prepareStatement(ConstantsJavaDB.QUERY_SEL_TABLE_CONTEXTO);
            rsSel = stmtSelect.executeQuery();
            ContextEntity ctxEntity = null;
            if (rsSel == null) {
                return ctxEntity;
            }
            while (rsSel.next()) {
                String pais = rsSel.getString(1);
                String idioma = rsSel.getString(2);
                String sep_mil = rsSel.getString(3);
                String sep_decimal = rsSel.getString(4);
                ctxEntity = new ContextEntity();
                ctxEntity.setCountry(pais);
                ctxEntity.setLanguage(idioma);
                ctxEntity.setSepMiles(sep_mil.charAt(0));
                ctxEntity.setSepDecimales(sep_decimal.charAt(0));
                return ctxEntity;
            }
            return ctxEntity;
        } catch (SQLException ex) {
            throw new SQLException("Error en obtención de configuración de contexto: " + ex.getMessage());
        } finally {
            if (stmtSelect != null && !stmtSelect.isClosed()) {
                stmtSelect.close();
            }
            if (rsSel != null && !rsSel.isClosed()) {
                rsSel.close();
            }
            if (dbConnection != null && !dbConnection.isClosed()) {
                dbConnection.close();
            }
        }
    }

    public static boolean insertConfiguracionContexto(ContextEntity ctxEntity) throws SQLException {
        PreparedStatement stmtSelect = null;
        Connection dbConnection = null;
        try {
            dbConnection = ConnectionDB.getConnection(false);
            stmtSelect = dbConnection.prepareStatement(ConstantsJavaDB.QUERY_INSERT_TABLE_CONTEXTO);
            stmtSelect.clearParameters();
            stmtSelect.setString(1, ctxEntity.getCountry());
            stmtSelect.setString(2, ctxEntity.getLanguage());
            stmtSelect.setString(3, String.valueOf(ctxEntity.getSepMiles()));
            stmtSelect.setString(4, String.valueOf(ctxEntity.getSepDecimales()));
            int countRows = stmtSelect.executeUpdate();
            if (countRows > 0) {
                return true;
            }
            return false;

        } catch (SQLException ex) {
            throw new SQLException("Error en obtención de configuración de contexto: " + ex.getMessage());
        } finally {
            if (stmtSelect != null && !stmtSelect.isClosed()) {
                stmtSelect.close();
            }
            if (dbConnection != null && !dbConnection.isClosed()) {
                dbConnection.close();
            }
        }
    }
    
    public static boolean updateConfiguracionContexto(ContextEntity ctxEntity) throws SQLException {
        PreparedStatement stmtSelect = null;
        Connection dbConnection = null;
        try {
            dbConnection = ConnectionDB.getConnection(false);
            stmtSelect = dbConnection.prepareStatement(ConstantsJavaDB.QUERY_UPDATE_TABLE_CONTEXTO);
            stmtSelect.clearParameters();
            stmtSelect.setString(1, ctxEntity.getCountry());
            stmtSelect.setString(2, ctxEntity.getLanguage());
            stmtSelect.setString(3, String.valueOf(ctxEntity.getSepMiles()));
            stmtSelect.setString(4, String.valueOf(ctxEntity.getSepDecimales()));
            stmtSelect.setInt(5, ConstantsJavaDB.ID_REGISTER_TABLE_CONTEXTO);
            int countRows = stmtSelect.executeUpdate();
            if (countRows > 0) {
                return true;
            }
            return false;

        } catch (SQLException ex) {
            throw new SQLException("Error en actualización de configuración de contexto: " + ex.getMessage());
        } finally {
            if (stmtSelect != null && !stmtSelect.isClosed()) {
                stmtSelect.close();
            }
            if (dbConnection != null && !dbConnection.isClosed()) {
                dbConnection.close();
            }
        }
    }
}
